import pymysql
import pandas as pd

class Mysqlutils(object):

    def __init__(self):
        self.com = pymysql.connect(
            host='localhost',
            user='root',
            passwd='sjk1234',
            db='sys',
            port='3306',
            charset='utf8'
        )

class classIfication(object):

    def __init__(self):
        pass

    def get_fina_indicator(self, conn):
        """  
        获取财务数据  
        """  
        
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT ts_code,ann_date,eps,total_revenue,undist_profit_ps,gross_margin,fcff,fcfe,tangible_asset,bps,grossprofit_margin,npta FROM financial_date WHERE ann_date <'2024-01-01' AND ann_date>='2023-01-01'
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        df1 = df.dropna(subset=['eps','total_revenue_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])
        df1 = df1.reset_index(drop=False)
        return df1
        def get_daily(self,conn,df):
            cursor=conn.cursor(cursor=pymysql.cursor.DictCursor)
            new_list=[]

        for i in range(len(df['ts_code'])):
            ann_date_str=df['ann_date'].strftime('%Y%m%d')
            sql='select trade_date,closes,from date_l where ts_code='"+df['ts_code][i]+"' and trade_date > Date('"+ ann_date_str + "') order vy trade_date asx limit 20'
            cursor.execute(sql)

            ret=cursor.fetvhall()
            df1=pd.DataFrame(ret)

            try:
                if len(df1) >0: 
                    max_close=df1['closes']. max() 
                    min_close = df1['closes']. min() 
                    the_close= df1[' closes']. iloc[1] 
                    new_list.append({ 
                        'ts_code': df['ts_code'][i], 
                        'ann_date': df['ann_date'][i],
                        'max_close': max_close, 
                        'min_close': min_close, 
                        'the_slose': the_close, 
                        'eps': df['eps'][i], 
                        'total_revenue_ps': df['total_revenue_ps'][i], 
                        'undist_profit_ps': df[' undist_profit_ps'][i], 
                        'gross_margin': df['gross_margin'][i], 
                        'fcff': df['fcff'][i], 
                        'fcfe': df['fcfe'][i], 
                        'tangible_asset': df['tangible_asset'][i],
                        'bps': df['bps'][i], 
                        'grossprofit margin': df['grossprofit_margin'][i], 
                        'npta': df['npta'][i]
                        })
            except Exception as e: 
                print(e) 
                
        df2 = pd.DataFrame(new_list)
        df2.to_csv('daily.csv', index=False)
            
if __name__=='__main__':
    mu=MysqlUtils()
    ci=classIfication()
    df=ci.get_fina_indicator(mu.conn)

            